# -*- coding: utf-8 -*-
"""
        "id": 497,
        "name": "SpaceXNaut Dog",
        "image_url": "https://racawebsource.s3.us-east-2.amazonaws.com/nft/spacex_dog_img.jpg",
        "count": 1,
        "fixed_price": "2000000",
        "highest_price": "0",
        "start_time": 1634175707,
        "end_time": -1,
        "status": "active",
        "sale_type": "fixed_price",
        "token_id": "9966",
        "sale_address": "0x0c09911e03cCC3a31db761a7948203F4b6248d8a"
table:
    market_place
        int id # 编号
        string name
        int count # 个数
        double fixed_price # 单价
        int start_time
        string status
        string token_id
        string time #时间

    market_place_last_time
        string time # 时间
        int total # 总数

    buy_now
        string id # 编号
        string time #时间

    price_limit
        string name # 产品类型
        double price

    switch
        string id
        bool spider_flag # 开启爬虫
        int spider_delay # 每次爬虫后等待时间 单位：秒


"""
import json
import logging
import threading
import sqlite3
import traceback

DATABASE_PATH = "../radiocaca.db"


class DataManager:
    def __init__(self, database_path=DATABASE_PATH):
        self.database_path = database_path
        self.lock = threading.Lock()

    def init_shop_dataBase(self):
        logging.info("init_shop_dataBase")
        self.lock.acquire()
        conn = sqlite3.connect(self.database_path)
        c = conn.cursor()
        c.execute('''CREATE TABLE IF NOT EXISTS 'market_place'(
                                id          INT     NOT NULL PRIMARY KEY,
                                name        TEXT    NOT NULL,
                                count       INT     ,
                                fixed_price DOUBLE  NOT NULL,
                                start_time  INT     ,
                                status      TEXT    ,
                                token_id    TEXT    ,
                                time        TEXT    
                             );''')
        c.execute('''CREATE TABLE IF NOT EXISTS 'market_place_stat' (
                                                                time    TEXT    NOT NULL,
                                                                name    TEXT    NOT NULL,
                                                                total   INT     NOT NULL,
                                                                min     DOUBLE  NOT NULL,
                                                                max     DOUBLE  NOT NULL,
                                                                average DOUBLE  NOT NULL,
                                                                median  DOUBLE  NOT NULL,
                                                                PRIMARY KEY (time, name)
                                                             );''')
        c.execute('''CREATE TABLE IF NOT EXISTS 'market_place_last_time' (
                                                        id      INT     NOT NULL PRIMARY KEY,
                                                        time    TEXT    NOT NULL,
                                                        total   INT     NOT NULL
                                                     );''')
        c.execute('''CREATE TABLE IF NOT EXISTS 'buy_now' (
                                                        id          TEXT    NOT NULL,
                                                        name        TEXT    NOT NULL,
                                                        fixed_price DOUBLE  NOT NULL,
                                                        diff_price  DOUBLE  NOT NULL,
                                                        time        TEXT    NOT NULL,
                                                        PRIMARY KEY (id, time)
                                                     );''')
        c.execute('''CREATE TABLE IF NOT EXISTS 'price_limit' (
                        name     TEXT    NOT NULL PRIMARY KEY,
                        price    DOUBLE  NOT NULL
                     );''')
        c.execute('''CREATE TABLE IF NOT EXISTS 'switch' (
                        id              TEXT    NOT NULL PRIMARY KEY,
                        spider_flag     BOOL    NOT NULL,
                        spider_delay    INT     NOT NULL
                     );''')

        conn.commit()
        conn.close()
        self.lock.release()

    def update_market_place(self, market_place, stime, total):
        logging.info("updateMarketPlace: " + str(total) + " [" + str(stime) + "]")
        self.lock.acquire()
        try:
            conn = sqlite3.connect(self.database_path)
            for item in market_place:
                if "fixed_price" in item and "id" in item and "name" in item and "count" in item and "status" in item:
                    conn.execute("REPLACE INTO 'market_place'(id, name, count, fixed_price, status, time) "
                                 "VALUES (?, ?, ?, ?, ?, ?);",
                             (item["id"], item["name"], item["count"], item["fixed_price"], item["status"], stime))
                else:
                    logging.warning(item)
            conn.execute("REPLACE INTO 'market_place_last_time'(id, time, total) VALUES (1, ?, ?);",
                         (stime, total))
            conn.commit()
            conn.close()
        except:
            traceback.print_exc()
        self.lock.release()

    def update_market_place_stat(self, stat, stime):
        logging.info("update_market_place_stat: " + "[" + str(stime) + "]")
        self.lock.acquire()
        conn = sqlite3.connect(self.database_path)
        for key, item in stat.items():
            conn.execute("REPLACE INTO 'market_place_stat'(name, total, min, max, average, median, time) "
                         "VALUES (?, ?, ?, ?, ?, ?, ?);",
                         (key, item["total"], item["min"], item["max"], item["average"], item["median"], stime))
        conn.commit()
        conn.close()
        self.lock.release()

    def get_latest_stat(self):
        logging.info("get_latest_stat")
        stat = None
        self.lock.acquire()
        conn = sqlite3.connect(self.database_path)
        ret = conn.execute("SELECT time FROM 'market_place_last_time' WHERE id=1;").fetchall()
        if len(ret) > 0:
            ret = conn.execute("SELECT name, total, min, max, average, median FROM 'market_place_stat' WHERE time=?;",
                               (ret[0][0],)).fetchall()
            if len(ret) > 0:
                stat = {}
                for item in ret:
                    stat[item[0]] = {
                        "total": item[1],
                        "min": item[2],
                        "max": item[3],
                        "average": item[4],
                        "median": item[5]
                    }
        conn.close()
        self.lock.release()
        return stat

    def del_market_place(self, m_ids):
        logging.info("del_market_place")
        self.lock.acquire()
        conn = sqlite3.connect(self.database_path)
        for m_id in m_ids:
            conn.execute("DELETE from 'market_place' where id=?;", (m_id,))
        conn.commit()
        conn.close()
        self.lock.release()

    def add_buy_now(self, buy_now, stime):
        logging.info("add_buy_now size: " + str(len(buy_now)))
        self.lock.acquire()
        conn = sqlite3.connect(self.database_path)
        for key, item in buy_now.items():
            conn.execute("REPLACE INTO 'buy_now'(id, name, fixed_price, diff_price, time) VALUES (?, ?, ?, ?, ?);",
                         (key, item["name"], item["fixed_price"], item["diff_price"], stime))
        conn.commit()
        conn.close()
        self.lock.release()

    def del_buy_now(self, m_id):
        logging.info("del_buy_now: " + m_id)
        self.lock.acquire()
        conn = sqlite3.connect(self.database_path)
        conn.execute("DELETE from 'buy_now' where id=?;", (m_id,))
        conn.commit()
        conn.close()
        self.lock.release()

    def get_buy_now(self):
        logging.info("get_buy_now")
        data = None
        self.lock.acquire()
        conn = sqlite3.connect(self.database_path)
        ret = conn.execute("SELECT time FROM 'market_place_last_time' WHERE id=1;").fetchall()
        if len(ret) > 0:
            ret = conn.execute("SELECT id, name, fixed_price, diff_price FROM 'buy_now' WHERE time=?;",
                               (ret[0][0],)).fetchall()
            if len(ret) > 0:
                data = {}
                for item in ret:
                    data[item[0]] = {
                        "name": item[1],
                        "fixed_price": item[2],
                        "diff_price": item[3]
                    }
        conn.close()
        self.lock.release()
        return data

    def update_price_limit(self, price_limit):
        logging.info("update_price_limit: " + "[" + str(price_limit) + "]")
        self.lock.acquire()
        conn = sqlite3.connect(self.database_path)
        for key, item in price_limit.items():
            conn.execute("REPLACE INTO 'price_limit'(name, price) VALUES (?, ?);",
                         (key, int(item)))
        conn.commit()
        conn.close()
        self.lock.release()

    def get_price_limit(self):
        logging.info("get_price_limit")
        price_limit = None
        self.lock.acquire()
        conn = sqlite3.connect(self.database_path)
        ret = conn.execute("SELECT name, price FROM 'price_limit';").fetchall()
        if len(ret) > 0:
            price_limit = {}
            for item in ret:
                price_limit[item[0]] = item[1]
        conn.close()
        self.lock.release()
        return price_limit

    def update_switch(self, switch):
        logging.info("update_switch: " + json.dumps(switch))
        self.lock.acquire()
        conn = sqlite3.connect(self.database_path)
        conn.execute("REPLACE INTO 'switch'(id, spider_flag, spider_delay) VALUES (1, ?, ?);",
                     (switch["spider_flag"], switch["spider_delay"]))
        conn.commit()
        conn.close()
        self.lock.release()

    def get_switch(self):
        logging.info("get_switch")
        data = None
        self.lock.acquire()
        conn = sqlite3.connect(self.database_path)
        ret = conn.execute("SELECT spider_flag, spider_delay FROM 'switch' WHERE id=1;").fetchall()
        if len(ret) > 0:
            data = {
                "spider_flag": ret[0][0],
                "spider_delay": ret[0][1]
            }
        conn.close()
        self.lock.release()
        return data

